﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

using System.Reflection;
using TMT_CMS.BL.Excel.AttributeUtility;

namespace TMT_CMS.BL.Excel
{
    public static class ExcelHelper
    {
        private const string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;";

        public static List<T> GetEntityFrom<T>(string filePath) where T : new()
        {
            OleDbConnection conn = null;
            try
            {
                conn = new OleDbConnection(string.Format(connectionString, filePath));
                conn.Open();

                DataTable worksheets = conn.GetSchema("Tables");
                string firstSheetName = string.Empty;
                if (worksheets.Rows.Count != 0)
                {
                    firstSheetName = worksheets.Rows[0]["TABLE_NAME"].ToString();
                    OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM [{0}]", firstSheetName), conn);
                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    List<T> resultList = new List<T>();
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        resultList.Add(AttributeHelper.ConvertFromDR<T>(dr, true));
                    }
                    return resultList;
                }
                else
                {
                    return new List<T>();
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        public static bool IsValidExcel(string filePath, Type excelEntityType, out string errorMsg)
        {
            OleDbConnection conn = null;
            errorMsg = string.Empty;
            try
            {
                conn = new OleDbConnection(string.Format(connectionString, filePath));
                conn.Open();

                // get first sheet name
                DataTable worksheets = conn.GetSchema("Tables");
                if (worksheets.Rows.Count == 0)
                {
                    errorMsg = "选择的Excel没有一个Sheet。";
                    return false;
                }

                // get column list from excel
                string firstSheetName = worksheets.Rows[0]["TABLE_NAME"].ToString();
                DataTable columns = conn.GetSchema("Columns", new string[] { null, null, firstSheetName, null });

                // get column list
                List<string> columnList = GetExpectColumnFromEnity(excelEntityType);
                foreach (string column in columnList)
                {
                    DataRow[] drs = columns.Select(string.Format("COLUMN_NAME = '{0}'", column));
                    if (drs.Length == 0)
                    {
                        errorMsg = string.Format("选择的Excel没有‘{0}’列，\r\n该Excel应该有这些列‘{1}’", column, string.Join(",", columnList.ToArray()));
                        return false;
                    }
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
            }

            return true;
        }

        private static List<string> GetExpectColumnFromEnity(Type type)
        {
            List<string> retList = new List<string>();

            PropertyInfo[] properties = type.GetProperties();
            if (properties != null && properties.Length != 0)
            {
                foreach (PropertyInfo property in properties)
                {
                    DataAttribute dataAttr = Attribute.GetCustomAttribute(property, typeof(DataAttribute)) as DataAttribute;
                    if (dataAttr != null && !string.IsNullOrEmpty(dataAttr.ExcelColumnName))
                    {
                        retList.Add(dataAttr.ExcelColumnName);
                    }
                }
            }

            return retList;
        }
    }
}
